<?php

namespace app\adminapi\logic\system\setting;

use adminmatrix\admin\logic\BaseLogic;
use think\facade\Db;
use think\Request;
use adminmatrix\admin\service\JsonService;
class DatabaseLogic extends BaseLogic
{
    public function getField(Request $request , ?string $tableName = null) : array
    {
        $page = 1;
        $pageSize = 10;
        $query = Db::table('information_schema.COLUMNS')-> where([
            ['TABLE_SCHEMA', '=' , env('DB_NAME')],
            ['TABLE_NAME' , '=' , $request->get('table_name' , $tableName)]
        ]);
        $lists = $query->field([
            'COLUMN_NAME as column_name',
            'COLUMN_TYPE as column_type',
            'IS_NULLABLE as is_nullable',
            'COLUMN_DEFAULT as column_default',
            'COLUMN_KEY as column_key',
            "COLUMN_COMMENT as column_coment",
            "EXTRA as extra"
        ])->fetchSql(false)->select();
        return $lists->toArray();
    }

    public function setField(Request $request){
        try {
            $tableName = $request->post('table_name');
            if (empty($tableName)) {
                throw new \Exception('表名不能为空');
            }

            $newFields = $request->post('fields');

            if (!is_array($newFields)) {
                // 尝试JSON解码
                $newFields = json_decode($newFields, true);
                if (json_last_error() !== JSON_ERROR_NONE || !is_array($newFields)) {
                    throw new \Exception('字段数据格式错误，必须是数组或有效的JSON字符串');
                }
            }

            if (empty($newFields)) {
                throw new \Exception('字段数据不能为空');
            }

            $currentFields = $this->getField($request, $tableName);
            
            // 将当前字段转换为以字段名为键的数组，方便查找
            $currentFieldMap = array_column($currentFields, null, 'column_name');
            
            // 收集需要执行的 SQL
            $alterSqls = [];
            
            foreach ($newFields as $field) {
                // 验证字段数据的完整性
                if (!isset($field['column_name']) || empty($field['column_name'])) {
                    throw new \Exception('字段名称不能为空');
                }
                if (!isset($field['type']) || empty($field['type'])) {
                    throw new \Exception("字段 {$field['column_name']} 的类型不能为空");
                }

                $columnName = $field['column_name'];
                $columnType = $this->buildColumnType($field);
                $nullable = $field['is_nullable'] === 'YES' ? 'NULL' : 'NOT NULL';
                $default = $this->buildDefaultValue($field);
                $comment = !empty($field['column_coment']) ? "COMMENT '" . addslashes($field['column_coment']) . "'" : '';
                $key = $this->buildKeyDefinition($field);
                
                // 处理自动递增
                $extra = !empty($field['extra']) ? $field['extra'] : '';
                
                if (isset($currentFieldMap[$columnName])) {
                    // 修改现有字段
                    $alterSqls[] = "MODIFY COLUMN `{$columnName}` {$columnType} {$nullable} {$default} {$comment}";
                    
                    // 如果键类型发生变化
                    if ($currentFieldMap[$columnName]['column_key'] !== ($field['column_key'] ?? '')) {
                        if (!empty($currentFieldMap[$columnName]['column_key'])) {
                            // 先删除旧的键
                            if ($currentFieldMap[$columnName]['column_key'] === 'PRI') {
                                $alterSqls[] = "DROP PRIMARY KEY";
                            } else if ($currentFieldMap[$columnName]['column_key'] === 'UNI') {
                                $alterSqls[] = "DROP INDEX `{$columnName}`";
                            }
                        }
                        // 添加新的键
                        if (!empty($key)) {
                            $alterSqls[] = $key;
                        }
                    }
                    
                    // 如果自动递增状态发生变化
                    if ($currentFieldMap[$columnName]['extra'] !== $extra) {
                        // 自动递增的变更已经在 buildColumnType 中处理了，不需要额外的 SQL
                    }
                } else {
                    // 添加新字段
                    $alterSqls[] = "ADD COLUMN `{$columnName}` {$columnType} {$nullable} {$default} {$comment}";
                    // 如果有键定义
                    if (!empty($key)) {
                        $alterSqls[] = "ADD " . $key;
                    }
                }
            }
            
            // 执行 ALTER TABLE
            if (!empty($alterSqls)) {
                $sql = "ALTER TABLE `{$tableName}` " . implode(", ", $alterSqls);
                Db::execute($sql);
                return JsonService::success('修改成功');
            }
            
            return JsonService::fail('修改失败');
        } catch (\Exception $e) {
            return JsonService::fail("修改表结构失败：" . $e->getMessage());
        }
    }

    /**
     * 构建字段类型定义
     */
    protected function buildColumnType(array $field): string
    {
        $type = strtoupper($field['type']);
        $length = $field['length'] ?? '';
        $scale = $field['scale'] ?? '';
        
        if (!empty($length)) {
            if (!empty($scale)) {
                $type .= "({$length},{$scale})";
            } else {
                $type .= "({$length})";
            }
        }
        
        if (!empty($field['unsigned']) && $field['unsigned']) {
            $type .= ' UNSIGNED';
        }
        
        // 处理自动递增
        if (!empty($field['extra']) && $field['extra'] === 'auto_increment') {
            $type .= ' AUTO_INCREMENT';
        }
        
        return $type;
    }

    /**
     * 构建默认值定义
     */
    protected function buildDefaultValue(array $field): string
    {
        // 如果是自动递增字段，不设置默认值
        if (!empty($field['extra']) && $field['extra'] === 'auto_increment') {
            return '';
        }
        
        if ($field['is_nullable'] === 'YES' && $field['column_default'] === null) {
            return 'DEFAULT NULL';
        }
        
        if ($field['column_default'] !== null) {
            $default = $field['column_default'];
            if (in_array(strtolower($field['type']), ['varchar', 'char', 'text'])) {
                $default = "'" . addslashes($default) . "'";
            }
            return "DEFAULT {$default}";
        }
        
        return '';
    }

    /**
     * 构建键定义
     */
    protected function buildKeyDefinition(array $field): string
    {
        // 如果是自动递增字段，必须是主键
        if (!empty($field['extra']) && $field['extra'] === 'auto_increment') {
            return "PRIMARY KEY (`{$field['column_name']}`)";
        }
        
        if ($field['column_key'] === 'PRI') {
            return "PRIMARY KEY (`{$field['column_name']}`)";
        } else if ($field['column_key'] === 'UNI') {
            return "UNIQUE KEY `{$field['column_name']}` (`{$field['column_name']}`)";
        }
        return '';
    }

    public function getTableList(Request $request) : array
    {
        $page = $request->get('current' , 1);
        $pageSize = $request->get('pageSize' , 10);
        $query = Db::table("INFORMATION_SCHEMA.TABLES")
            ->where("TABLE_SCHEMA" , env("DB_NAME"))->fetchSql(false)
            ->limit(($page - 1) * $pageSize , $pageSize)->select();

        return [
            'lists' =>  $query->toArray(),
            'total' =>  Db::table("INFORMATION_SCHEMA.TABLES")
                ->where("TABLE_SCHEMA" , env("DB_NAME"))->fetchSql(false)->count(),
            'current'  =>   $page,
            'pageSize'  =>  $pageSize
        ];
    }


    public function migrationsList(Request $request) : array
    {
        $migrations = new \app\adminapi\model\system\database\Migrations;
        $page = (int)$request->get('current', 1);
        $pageSize = (int)$request->get('pageSize', 10);
        $golb = glob(App()->getRootPath() . '*/*/database/migrations/*.php');
        $all = [];
        foreach ($golb as $key => $value) {
            $value = str_replace(App()->getRootPath(), '', $value);
            $value = str_replace('\\', '/', $value);
            $value = str_replace('.php', '', $value);
            $pathArr = explode('/', $value);
            // 获取应用名
            $appName = '';
            $appIndex = array_search('app', $pathArr);
            if ($appIndex !== false && isset($pathArr[$appIndex + 1])) {
                $appName = $pathArr[$appIndex + 1];
            }
            $fileName = $pathArr[count($pathArr) - 1];

            if (preg_match('/^(\d+)_?(.*)$/', $fileName, $matches)) {
                $version = $matches[1];
                $name = $matches[2];
            } else {
                $version = '';
                $name = $fileName;
            }

            $migrationName = lcfirst(str_replace(' ', '', ucwords(str_replace('_', ' ', $name))));
            $migration = $migrations->where('version', $version)->findOrEmpty();
            $all[] = [
                'path' => $value,
                'version' => $version,
                'migration_name' => $migrationName,
                'app' => $appName,
                'start_time' => $migration->isEmpty() ? '' : $migration->start_time,
                'end_time' => $migration->isEmpty() ? '' : $migration->end_time,
                'is_install' => !$migration->isEmpty()
            ];
        }
        $total = count($all);
        $lists = array_slice($all, ($page - 1) * $pageSize, $pageSize);
        return [
            'lists' => $lists,
            'total' => $total,
            'current' => $page,
            'pageSize' => $pageSize
        ];
    }
}